database_name = "db.sqlite"
query = """
CREATE TABLE products_groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name VARCHAR(250) NOT NULL
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_group INTEGER NOT NULL,
    unit VARCHAR(20),
    name VARCHAR(250) NOT NULL
    CONSTRAINT fk_product_group_id
    REFERENCES products_groups(id)
);

-- Triggers for products - products_groups
CREATE TRIGGER fki_products_products_groups_id
BEFORE INSERT ON products
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'insert on table "products" violates foreign key constraint "fk_product_group_id"')
    WHERE  NEW.product_group IS NOT NULL
        AND (SELECT id FROM products_groups WHERE id = new.product_group) IS NULL;
END;

CREATE TRIGGER fku_products_products_groups_id
BEFORE UPDATE ON products
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "products" violates foreign key constraint "fk_product_group_id"')
    WHERE  NEW.product_group IS NOT NULL
        AND (SELECT id FROM products_groups WHERE id = new.product_group) IS NULL;
END;

CREATE TRIGGER fkd_products_products_groups_id
BEFORE DELETE ON products_groups
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'delete on table "products_groups" violates foreign key constraint "fk_product_group_id"')
    WHERE (SELECT product_group FROM products WHERE product_group = OLD.id) IS NOT NULL;
END;

CREATE TABLE providers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    short_name VARCHAR(100) NOT NULL,
    full_name VARCHAR(500),
    address VARCHAR(500),
    inn VARCHAR(20),
    kpp VARCHAR(20),
    account VARCHAR(25),
    bank VARCHAR(250)
);

-- table for income
CREATE TABLE income (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product INTEGER,
    provider INTEGER,
    date DATE,
    nds FLOAT,
    count INTEGER,
    price FLOAT
    CONSTRAINT fk_products_id
    REFERENCES products(id)    
    CONSTRAINT fk_providers_id
    REFERENCES providers(id)    
);

-- Trigger for income - providers
CREATE TRIGGER fki_income_providers_id
BEFORE INSERT ON income
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'insert on table "income" violates foreign key constraint "fk_providers_id"')
    WHERE  NEW.provider IS NOT NULL
        AND (SELECT id FROM providers WHERE id = new.provider) IS NULL;
END;

CREATE TRIGGER fku_income_providers_id
BEFORE UPDATE ON income
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "income" violates foreign key constraint "fk_providers_id"')
    WHERE  NEW.product_group IS NOT NULL
        AND (SELECT id FROM providers WHERE id = new.provider) IS NULL;
END;

CREATE TRIGGER fkd_income_providers_id
BEFORE DELETE ON providers
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'delete on table "providers" violates foreign key constraint "fk_providers_id"')
    WHERE (SELECT provider FROM income WHERE provider = OLD.id) IS NOT NULL;
END;

-- Trigger for income - products
CREATE TRIGGER fki_income_products_id
BEFORE INSERT ON income
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'insert on table "income" violates foreign key constraint "fk_products_id"')
    WHERE  NEW.product IS NOT NULL
        AND (SELECT id FROM products WHERE id = new.product) IS NULL;
END;

CREATE TRIGGER fku_income_products_id
BEFORE UPDATE ON income
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "income" violates foreign key constraint "fk_products_id"')
    WHERE  NEW.product_group IS NOT NULL
        AND (SELECT id FROM providers WHERE id = new.provider) IS NULL;
END;

CREATE TRIGGER fkd_income_products_id
BEFORE DELETE ON products
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'delete on table "products" violates foreign key constraint "fk_products_id"')
    WHERE (SELECT name FROM products WHERE name = OLD.id) IS NOT NULL;
END;




"""

import sqlite3 as db
def exec_sql(query):
    try:
        c = db.connect(database=database_name)
        cu = c.cursor()
        cu.execute(query)
        c.commit()
        c.close()
    except db.DatabaseError, x:
        print "db error:", x
    except:
        print 'error'
        
for q in query.split("\n\n"): exec_sql(q)
